package dfbz.com.dao;

import dfbz.com.constant.Const;
import dfbz.com.dao.impl.BaseDaoImpl;
import dfbz.com.model.Conference;
import dfbz.com.model.Dept;
import dfbz.com.util.JDBCUtil;
import org.apache.commons.dbutils.*;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class ConferenceDao extends BaseDaoImpl {

    public List<Map<String, Object>> show(Integer deptId,int page,String search){
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        List<Map<String, Object>> maps = new ArrayList<>();
        try {
            StringBuilder sql = new StringBuilder();
            if (deptId==0){
                sql.append("select id,dept_id as did, dept_name as dname,title,content,publish_date as pdate,\n" +
                        "      start_time as  stime,status\n" +
                        "from conference\n" );
                if (null == search) {
                    sql.append("order by id desc limit ?,? ");
                    maps = qr.query(sql.toString(), new MapListHandler(),  (page - 1) * Const.MAX_PAGE_SIZE, Const.MAX_PAGE_SIZE);
                } else {
                    sql.append("where title like ? ");
                    sql.append("order by id limit ?,? ");
                    search ="%"+ search + "%";
                    maps = qr.query(sql.toString(), new MapListHandler(),  search,  (page - 1) * Const.MAX_PAGE_SIZE, Const.MAX_PAGE_SIZE);
                }

            }
            else {//根据部门id查找
                sql.append("select id,dept_id as did, dept_name as dname,title,content,publish_date as pdate,\n" +
                        "      start_time as  stime,status\n" +
                        "from conference\n" +
                        "where dept_id=? ");
                if (null == search) {
                    sql.append("order by id limit ?,? ");
                    maps = qr.query(sql.toString(), new MapListHandler(), deptId, (page - 1) * Const.MAX_PAGE_SIZE, Const.MAX_PAGE_SIZE);
                } else {
                    sql.append("and title like ? ");
                    sql.append("order by id limit ?,? ");
                    search ="%"+ search + "%";
                    maps = qr.query(sql.toString(), new MapListHandler(), deptId, search,  (page - 1) * Const.MAX_PAGE_SIZE, Const.MAX_PAGE_SIZE);
                }
            }



        } catch (SQLException e) {
            e.printStackTrace();
        }
        return maps;
}


    public long findTotalConferenceCount(Integer deptId) {
        long total = 0L;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        Map<String, Object> map =null;
                String sql=null;
        try {
            if (0==deptId){
              sql = "select count(*) as total from conference";
                map= qr.query(sql, new MapHandler());
            }else {
                sql = "select count(*) as total from conference where dept_id=?";
                map= qr.query(sql, new MapHandler(),deptId);
            }

            total = Long.parseLong(map.get("total").toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }


    //发布新会议
    public void add(String deptName, Integer deptId, String title, String content, Date pdate
            , String startTime, Integer status,long total) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String pd = sdf.format(pdate);
        try {
            content = URLDecoder.decode(content,"UTF-8");
            title= URLDecoder.decode(title,"UTF-8");

            startTime = URLDecoder.decode(startTime, "UTF-8");
            String   sql = "insert into \n" +
                    "conference\n" +
                    "values(?,?,?,?,?,?,?,?)";
            qr.update(sql,total,deptName,deptId,title,content,pd,startTime,status);
        } catch (Exception e) {
            e.printStackTrace();
        }


    }

    public String getNameById(Integer deptId) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        String sql="select name from dept where id=?";
        Map<String, Object> dept = null;
        String deptName=null;
        try {
            dept = qr.query(sql, new MapHandler(), deptId);
            deptName = dept.get("name").toString();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return deptName;
    }

    public void updateStatus() {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        //开启下划线->驼峰转换所用
        RowProcessor processor = new BasicRowProcessor(new GenerousBeanProcessor());
        List<Conference> meetings=null;
        String sql=null;
        try {
             sql="SELECT *\n" +
                    "from conference";


            meetings = qr.query(sql, new BeanListHandler<>(Conference.class, processor));
            Date now = new Date();//当前时间
            for (Conference con:meetings) {
                Date pdate = con.getPublishDate();
                Date startTime = con.getStartTime();
                Integer id = con.getId();
                if(null!=pdate&&null!=startTime){
                    SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
                    String time1 = sdf1.format(pdate);
                    SimpleDateFormat sdf2 = new SimpleDateFormat("HH:mm:ss");
                    String time2 = sdf2.format(startTime);
                    SimpleDateFormat fmt =new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    Date date = fmt.parse(time1+" "+time2);//获取了拼接好的时间

                    if (date.getTime()<now.getTime()){
                        if (sdf1.format(now).equals(time1)){//同一天
                            sql="update conference set status=1\n" +
                                    "\n" +
                                    "where id="+id;
                            qr.update(sql);
                        }else{
                            sql="update conference set status=2\n" +
                                    "\n" +
                                    "where id="+id;
                            qr.update(sql);
                        }


                }



                }
            }



        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public Map<String, Object> getMeetById(Integer id) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        Map<String, Object> map = null;

             String  sql="select id,dept_name as dname,dept_id as did,title,content,publish_date as pdate,\n" +
                   "start_time as starttime,status\n" +
                   "from conference\n" +
                   "where id=?";
        try {
            map = qr.query(sql, new MapHandler(), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return map;

    }
}
